﻿-- Effacement complet des tables
--DROP TABLE PRODUIT;
--DROP TABLE PRODUIT_HIST;
--DROP TABLE CATEGORIE;
--DROP TABLE CATEGORIE_HIST;

-- Création de la table CATEGORIE
CREATE TABLE CATEGORIE (
  ID_CAT SERIAL, -- auto-incrémentation
  Intitule_CAT VARCHAR(50) NOT NULL,
  Description_CAT VARCHAR(200),
  Modification_CAT TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Date actuelle par défaut
  PID_CAT INTEGER DEFAULT NULL -- Référence de la catégorie parente
);

-- Création de la clé primaire
ALTER TABLE CATEGORIE
  ADD CONSTRAINT PK_CATEGORIE
    PRIMARY KEY(ID_CAT);

-- Création de la relation cyclique
ALTER TABLE CATEGORIE
  ADD CONSTRAINT FK_CATEGORIE_CATEGORIE
    FOREIGN KEY (PID_CAT)
	REFERENCES CATEGORIE (ID_CAT);

-- Commentaires textuels
COMMENT ON TABLE CATEGORIE IS 'Catégories dans lesquelles sont rangés les produits';
COMMENT ON COLUMN CATEGORIE.ID_CAT IS 'Identifiant de chaque catégorie dans la DB';
COMMENT ON COLUMN CATEGORIE.Intitule_CAT IS 'Intitulé de la catégorie';
COMMENT ON COLUMN CATEGORIE.Description_CAT IS 'Description plus détaillée de la catégorie';
COMMENT ON COLUMN CATEGORIE.Modification_CAT IS 'Date de modification de la catégorie dans la DB';
COMMENT ON COLUMN CATEGORIE.PID_CAT IS 'Identifiant de la catégorie parente';




-- Création de la table CATEGORIE_HIST (historique de CATEGORIE)
CREATE TABLE CATEGORIE_HIST (
  ID_CATHIST SERIAL,
  ID_CAT INTEGER,
  Intitule_CAT VARCHAR(50) NOT NULL,
  Description_CAT VARCHAR(200),
  Modification_CAT TIMESTAMP NOT NULL,
  PID_CAT INTEGER -- Référence de la catégorie parente
);

-- Création de la clé primaire
ALTER TABLE CATEGORIE_HIST
  ADD CONSTRAINT PK_CATEGORIE_HIST
    PRIMARY KEY(ID_CATHIST);

-- Commentaires textuels
COMMENT ON TABLE CATEGORIE_HIST IS 'Table contenant toutes les modifications sur la table CATEGORIE';

-- Création des index sur la table CATEGORIE_HIST
CREATE INDEX IDX_ID_CATHIST ON CATEGORIE_HIST (ID_CAT);
CREATE INDEX IDX_DATE_CATHIST ON CATEGORIE_HIST (Modification_CAT);





-- Création de la table PRODUIT
CREATE TABLE PRODUIT (
  ID_PRO SERIAL, -- auto-incrémentation
  Intitule_PRO VARCHAR(50) NOT NULL,
  Description_PRO VARCHAR(200),
  Prix_PRO NUMERIC(8,2) NOT NULL, -- Prix HTVA
  StockRestant_PRO INTEGER DEFAULT 0 NOT NULL,
  Modification_PRO TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- Date actuelle par défaut
  ID_CAT INTEGER -- Référence de la catégorie à laquelle le produit appartient
  );

-- Création de la clé primaire
ALTER TABLE PRODUIT
  ADD CONSTRAINT PK_PRODUIT
    PRIMARY KEY(ID_PRO);


-- Création de la relation entre un produit et une (sous-)catégorie
ALTER TABLE PRODUIT
  ADD CONSTRAINT FK_PRODUIT_CATEGORIE
    FOREIGN KEY (ID_CAT)
	REFERENCES CATEGORIE (ID_CAT);

-- Commentaires textuels
COMMENT ON TABLE PRODUIT IS 'Table contenant tous les produits proposés par BMG';
COMMENT ON COLUMN PRODUIT.ID_PRO IS 'Identifiant de chaque produit dans la DB';
COMMENT ON COLUMN PRODUIT.ID_CAT IS 'Identifiant de chaque catégorie dans la DB';
COMMENT ON COLUMN PRODUIT.Intitule_PRO IS 'Intitulé du produit';
COMMENT ON COLUMN PRODUIT.Modification_PRO IS 'Date de modification du produit dans la DB';
COMMENT ON COLUMN PRODUIT.StockRestant_PRO IS 'Quantité du produit restant en stock';
COMMENT ON COLUMN PRODUIT.Description_PRO IS 'Description plus détaillée du produit';
COMMENT ON COLUMN PRODUIT.Prix_PRO IS 'Prix du produit HTVA';





-- Création de la table PRODUIT_HIST (Historique de PRODUIT)
CREATE TABLE PRODUIT_HIST (
  ID_PROHIST SERIAL,
  ID_PRO INTEGER, 
  Intitule_PRO VARCHAR(50) NOT NULL,
  Description_PRO VARCHAR(200),
  Prix_PRO NUMERIC(8,2) NOT NULL, -- Prix HTVA
  StockRestant_PRO INTEGER DEFAULT 0 NOT NULL,
  Modification_PRO TIMESTAMP NOT NULL,
  ID_CAT INTEGER -- Référence de la catégorie à laquelle le produit appartient
  );
  
  -- Création de la clé primaire
ALTER TABLE PRODUIT_HIST
  ADD CONSTRAINT PK_PRODUIT_HIST
    PRIMARY KEY(ID_PROHIST);
  
-- Commentaires textuels
COMMENT ON TABLE PRODUIT_HIST IS 'Table contenant toutes les modifications sur la table PRODUIT';

-- Création des index sur la table PRODUIT_HIST
CREATE INDEX IDX_ID_PROHIST ON PRODUIT_HIST (ID_PRO);
CREATE INDEX IDX_DATE_PROHIST ON PRODUIT_HIST (Modification_PRO);





-- Création des fonctions

--- Fonction GetCategorieList
CREATE OR REPLACE FUNCTION GetCategorieList() RETURNS TABLE (
  ID_CAT INTEGER,
  Intitule_CAT VARCHAR(50),
  Description_CAT VARCHAR(200),
  PID_CAT INTEGER
  )
AS $$
  BEGIN
  RETURN QUERY 
  SELECT c.ID_CAT, c.Intitule_CAT, c.Description_CAT, c.Pid_CAT 
    FROM CATEGORIE c
	ORDER BY c.Intitule_CAT; 
  END;
  $$
LANGUAGE plpgsql
;



--- Fonction GetCategorieList avec paramètre texte (intitulé ou descriptif)
CREATE OR REPLACE FUNCTION GetCategorieList(DescriptionNom TEXT) RETURNS TABLE (
  ID_CAT INTEGER,
  Intitule_CAT VARCHAR(50),
  Description_CAT VARCHAR(200),
  PID_CAT INTEGER
  )
AS $$
  BEGIN
  RETURN QUERY
  SELECT c.ID_CAT, c.Intitule_CAT, c.Description_CAT, c.Pid_CAT 
    FROM CATEGORIE c 
    WHERE unaccent(lower(c.Intitule_CAT)) LIKE '%' || unaccent(lower($1)) || '%' 
      OR unaccent(lower(c.Description_CAT)) LIKE '%' || unaccent(lower($1)) || '%'
	ORDER BY c.Intitule_CAT;
  END;	
  $$
LANGUAGE plpgsql
;



--- Fonction GetCategorieList avec paramètres texte (intitulé ou descriptif) et entier (catégorie parente)
CREATE OR REPLACE FUNCTION GetCategorieList(DescriptionNom TEXT, IDparent INTEGER) RETURNS TABLE (
  ID_CAT INTEGER,
  Intitule_CAT VARCHAR(50),
  Description_CAT VARCHAR(200),
  PID_CAT INTEGER
  )
AS $$
  BEGIN
  RETURN QUERY
  SELECT c.ID_CAT, c.Intitule_CAT, c.Description_CAT, c.Pid_CAT 
    FROM CATEGORIE c 
    WHERE (unaccent(lower(c.Intitule_CAT)) LIKE '%' || unaccent(lower($1)) || '%' 
      OR unaccent(lower(c.Description_CAT)) LIKE '%' || unaccent(lower($1)) || '%')
	  AND c.Pid_CAT = $2
	ORDER BY c.Intitule_CAT; 
  END;
  $$
LANGUAGE plpgsql
;



--- Fonction GetCategorie avec paramètre entier (ID catégorie)
CREATE OR REPLACE FUNCTION GetCategorie(IDcategorie INTEGER) RETURNS TABLE (
  ID_CAT INTEGER,
  Intitule_CAT VARCHAR(50),
  Description_CAT VARCHAR(200),
  PID_CAT INTEGER,
  Modification_CAT timestamp
  )
AS $$
  BEGIN
  RETURN QUERY
  SELECT c.ID_CAT, c.Intitule_CAT, c.Description_CAT, c.Pid_CAT, c.Modification_CAT 
    FROM CATEGORIE c
  WHERE c.ID_CAT = $1;
  END;  
  $$
LANGUAGE plpgsql
;


--- Fonction InsertCategorie avec paramètres texte (intitule), texte (description), entier (ID catégorie parente)
CREATE OR REPLACE FUNCTION InsertCategorie(Intitule TEXT, Description TEXT, PID INTEGER) RETURNS VOID
AS $$
  BEGIN
  INSERT INTO CATEGORIE (Intitule_CAT, Description_CAT, PID_CAT)
    VALUES ($1, $2, $3);
  END;
  $$
LANGUAGE plpgsql
;



--- Fonction DeleteCategorie avec paramètre entier (ID catégorie)
CREATE OR REPLACE FUNCTION DeleteCategorie(IDcategorie INTEGER) RETURNS VOID
AS $$
  BEGIN
  DELETE FROM CATEGORIE c
    WHERE c.ID_CAT = $1;
  END;
  $$
LANGUAGE plpgsql
;



--- Fonction UpdateCategorie avec paramètres entier (ID catgorie), texte (intitule), texte (description), entier (ID catégorie parente)
CREATE OR REPLACE FUNCTION UpdateCategorie(IDcategorie INTEGER, Intitule TEXT, Description TEXT, PID INTEGER) RETURNS VOID
AS $$
  BEGIN
  INSERT INTO CATEGORIE_HIST (ID_CAT, Intitule_CAT, Description_CAT, Modification_CAT, PID_CAT)
	SELECT c.ID_CAT, c.Intitule_CAT, c.Description_CAT, CURRENT_TIMESTAMP, PID_CAT
	FROM CATEGORIE c
	WHERE c.ID_CAT = $1
	;
  UPDATE CATEGORIE
    SET Intitule_CAT = $2,
	  Description_CAT = $3,
	  PID_CAT = $4,
	  Modification_CAT = CURRENT_TIMESTAMP
	WHERE ID_CAT = $1;
  END;
  $$
LANGUAGE plpgsql
;



--- Fonction GetCategorieHistorique avec paramètres entier et dates (ID catégorie, date basse, date haute)
CREATE OR REPLACE FUNCTION GetCategorieHistorique(IDcategorie INTEGER, dateMin DATE, dateMax DATE) RETURNS TABLE (
  ID_CAT INTEGER,
  Intitule_CAT VARCHAR(50),
  Description_CAT VARCHAR(200),
  Pid_CAT INTEGER,
  Modification_CAT TIMESTAMP
  )
AS $$
  BEGIN
  RETURN QUERY
  SELECT ch.ID_CAT, ch.Intitule_CAT, ch.Description_CAT, ch.Pid_CAT, ch.Modification_CAT 
    FROM CATEGORIE_HIST ch
  WHERE ch.ID_CAT = $1 AND ch.Modification_CAT BETWEEN $2 AND $3+1
  ORDER BY ch.Modification_CAT DESC;
  END;  
  $$
LANGUAGE plpgsql
;



--- Fonction GetCategorieHistorique avec paramètres dates (date basse et date haute)
CREATE OR REPLACE FUNCTION GetCategorieHistorique(dateMin DATE, dateMax DATE) RETURNS TABLE (
  ID_CATHIST INTEGER,
  ID_CAT INTEGER,
  Intitule_CAT VARCHAR(50),
  Description_CAT VARCHAR(200),
  Pid_CAT INTEGER,
  Modification_CAT TIMESTAMP
  )
AS $$
  BEGIN
  RETURN QUERY
  SELECT ch.ID_CATHIST, ch.ID_CAT, ch.Intitule_CAT, ch.Description_CAT, ch.Pid_CAT, ch.Modification_CAT 
    FROM CATEGORIE_HIST ch
  WHERE ch.Modification_CAT BETWEEN $1 AND $2+1
  ORDER BY ch.ID_CATHIST DESC;
  END;  
  $$
LANGUAGE plpgsql
;





--- Fonction GetProduitList
CREATE OR REPLACE FUNCTION GetProduitList() RETURNS TABLE (
  ID_PRO INTEGER,
  Intitule_PRO VARCHAR(50),
  Description_PRO VARCHAR(200),
  Prix_PRO NUMERIC(8,2),
  StockRestant_PRO INTEGER,
  Modification_PRO TIMESTAMP,
  ID_CAT INTEGER,
  Intitule_CAT VARCHAR(50)
  )
AS $$
  BEGIN
  RETURN QUERY 
  SELECT p.ID_PRO, p.Intitule_PRO, p.Description_PRO, p.Prix_PRO, p.StockRestant_PRO, p.Modification_PRO, p.ID_CAT, c.Intitule_CAT
    FROM PRODUIT p
  INNER JOIN CATEGORIE c
  ON p.id_cat = c.id_cat
  ORDER BY p.Intitule_PRO; 
  END;
  $$
LANGUAGE plpgsql
;



--- Fonction GetProduitList avec paramètre texte (intitulé ou descriptif)
CREATE OR REPLACE FUNCTION GetProduitList(DescriptionNom TEXT) RETURNS TABLE (
  ID_PRO INTEGER,
  Intitule_PRO VARCHAR(50),
  Description_PRO VARCHAR(200),
  Prix_PRO NUMERIC(8,2),
  StockRestant_PRO INTEGER,
  Modification_PRO TIMESTAMP,
  ID_CAT INTEGER,
  Intitule_CAT VARCHAR(50)
  )
AS $$
  BEGIN
  RETURN QUERY
  SELECT p.ID_PRO, p.Intitule_PRO, p.Description_PRO, p.Prix_PRO, p.StockRestant_PRO, p.Modification_PRO, p.ID_CAT, c.Intitule_CAT
    FROM PRODUIT p
  INNER JOIN CATEGORIE c
  ON p.id_cat = c.id_cat
    WHERE unaccent(lower(p.Intitule_PRO)) LIKE '%' || unaccent(lower($1)) || '%' 
      OR unaccent(lower(p.Description_PRO)) LIKE '%' || unaccent(lower($1)) || '%'
	ORDER BY p.Intitule_PRO;
  END;	
  $$
LANGUAGE plpgsql
;



--- Fonction GetProduitList avec paramètres texte et entier (intitulé ou descriptif, catégorie)
CREATE OR REPLACE FUNCTION GetProduitList(DescriptionNom TEXT, IDcategorie INTEGER) RETURNS TABLE (
  ID_PRO INTEGER,
  Intitule_PRO VARCHAR(50),
  Description_PRO VARCHAR(200),
  Prix_PRO NUMERIC(8,2),
  StockRestant_PRO INTEGER,
  Modification_PRO TIMESTAMP,
  ID_CAT INTEGER,
  Intitule_CAT VARCHAR(50)
  )
AS $$
  BEGIN
  RETURN QUERY
  SELECT p.ID_PRO, p.Intitule_PRO, p.Description_PRO, p.Prix_PRO, p.StockRestant_PRO, p.Modification_PRO, p.ID_CAT, c.Intitule_CAT
    FROM PRODUIT p
  INNER JOIN CATEGORIE c
  ON c.id_cat = p.id_cat
    WHERE (unaccent(lower(p.Intitule_PRO)) LIKE '%' || unaccent(lower($1)) || '%' 
      OR unaccent(lower(p.Description_PRO)) LIKE '%' || unaccent(lower($1)) || '%')
	  AND p.ID_CAT = $2
	ORDER BY p.Intitule_PRO;
  END;	
  $$
LANGUAGE plpgsql
;



--- Fonction GetProduit avec paramètre entier (identifiant du produit)
CREATE OR REPLACE FUNCTION GetProduit(IDproduit INTEGER) RETURNS TABLE (
  ID_PRO INTEGER,
  Intitule_PRO VARCHAR(50),
  Description_PRO VARCHAR(200),
  Prix_PRO NUMERIC(8,2),
  StockRestant_PRO INTEGER,
  Modification_PRO TIMESTAMP,
  ID_CAT INTEGER
  )
AS $$
  BEGIN
  RETURN QUERY
  SELECT p.ID_PRO, p.Intitule_PRO, p.Description_PRO, p.Prix_PRO, p.StockRestant_PRO, p.Modification_PRO, p.ID_CAT
    FROM PRODUIT p
    WHERE p.ID_PRO = $1
  ORDER BY p.Intitule_PRO;
  END;	
  $$
LANGUAGE plpgsql
;



--- Fonction InsertProduit avec paramètres texte (intitule), texte (description), decimal (prix), entier (stock), entier (ID catégorie)
CREATE OR REPLACE FUNCTION InsertProduit(Intitule TEXT, Description TEXT, Prix NUMERIC(8,2), Stock INTEGER, IDcategorie INTEGER) RETURNS VOID
AS $$
  BEGIN
  INSERT INTO PRODUIT (Intitule_PRO, Description_PRO, Prix_PRO, StockRestant_PRO, ID_CAT)
    VALUES ($1, $2, $3, $4, $5);
  END;
  $$
LANGUAGE plpgsql
;



--- Fonction DeleteProduit avec paramètre entier (ID produit)
CREATE OR REPLACE FUNCTION DeleteProduit(IDproduit INTEGER) RETURNS VOID
AS $$
  BEGIN
  DELETE FROM PRODUIT p
    WHERE p.ID_PRO = $1;
  END;
  $$
LANGUAGE plpgsql
;



--- Fonction UpdateProduit avec paramètres entier (ID produit), texte (intitule), texte (description), decimal (prix), entier (stock), entier (ID catégorie)
CREATE OR REPLACE FUNCTION UpdateProduit(IDproduit INTEGER, Intitule TEXT, Description TEXT, Prix NUMERIC(8,2), Stock INTEGER, IDcategorie INTEGER) RETURNS VOID
AS $$
  BEGIN
  INSERT INTO PRODUIT_HIST (ID_PRO, Intitule_PRO, Description_PRO, Prix_PRO, StockRestant_PRO, Modification_PRO, ID_CAT)
	SELECT p.ID_PRO, p.Intitule_PRO, p.Description_PRO, p.Prix_PRO, p.StockRestant_PRO, CURRENT_TIMESTAMP, ID_CAT
	FROM PRODUIT p
	WHERE p.ID_PRO = $1
	;
  UPDATE PRODUIT
    SET Intitule_PRO = $2,
	  Description_PRO = $3,
	  Prix_PRO = $4,
	  StockRestant_PRO = $5,
	  Modification_PRO = CURRENT_TIMESTAMP,
	  ID_CAT = $6
	WHERE ID_PRO = $1;
  END;
  $$
LANGUAGE plpgsql
;



--- Fonction UpdateProduitPrix avec paramètres entier (ID produit), decimal (prix)
CREATE OR REPLACE FUNCTION UpdateProduitPrix(IDproduit INTEGER, Prix NUMERIC(8,2)) RETURNS VOID
AS $$
  BEGIN
  INSERT INTO PRODUIT_HIST (ID_PRO, Intitule_PRO, Description_PRO, Prix_PRO, StockRestant_PRO, Modification_PRO, ID_CAT)
	SELECT p.ID_PRO, p.Intitule_PRO, p.Description_PRO, p.Prix_PRO, p.StockRestant_PRO, CURRENT_TIMESTAMP, ID_CAT
	FROM PRODUIT p
	WHERE p.ID_PRO = $1
	;
  UPDATE PRODUIT
    SET Prix_PRO = $2,
	  Modification_PRO = CURRENT_TIMESTAMP
	WHERE ID_PRO = $1;
  END;
  $$
LANGUAGE plpgsql
;



--- Fonction UpdateProduitStock avec paramètres entier (ID produit), entier (stock)
CREATE OR REPLACE FUNCTION UpdateProduitStock(IDproduit INTEGER, stock INTEGER) RETURNS VOID
AS $$
  BEGIN
  INSERT INTO PRODUIT_HIST (ID_PRO, Intitule_PRO, Description_PRO, Prix_PRO, StockRestant_PRO, Modification_PRO, ID_CAT)
	SELECT p.ID_PRO, p.Intitule_PRO, p.Description_PRO, p.Prix_PRO, p.StockRestant_PRO, CURRENT_TIMESTAMP, ID_CAT
	FROM PRODUIT p
	WHERE p.ID_PRO = $1
	;
  UPDATE PRODUIT
    SET StockRestant_PRO = $2,
	  Modification_PRO = CURRENT_TIMESTAMP
	WHERE ID_PRO = $1;
  END;
  $$
LANGUAGE plpgsql
;

-- Function: UpdateProduitCat(integer, integer)
CREATE OR REPLACE FUNCTION UpdateProduitCat(IDproduit integer, IDcat integer)
  RETURNS void AS $$
  BEGIN
  INSERT INTO PRODUIT_HIST (ID_PRO, Intitule_PRO, Description_PRO, Prix_PRO, StockRestant_PRO, Modification_PRO, ID_CAT)
	SELECT p.ID_PRO, p.Intitule_PRO, p.Description_PRO, p.Prix_PRO, p.StockRestant_PRO, CURRENT_TIMESTAMP, ID_CAT
	FROM PRODUIT p
	WHERE p.ID_PRO = $1
	;
  UPDATE PRODUIT
    SET id_cat = $2,
	  Modification_PRO = CURRENT_TIMESTAMP
	WHERE ID_PRO = $1;
  END;
  $$
  LANGUAGE plpgsql
  ;



--- Fonction GetProduitHistorique avec paramètres entier et dates (ID produit, date basse, date haute)
CREATE OR REPLACE FUNCTION GetProduitHistorique(IDproduit INTEGER, dateMin DATE, dateMax DATE) RETURNS TABLE (
  ID_PRO INTEGER,
  Intitule_PRO VARCHAR(50),
  Description_PRO VARCHAR(200),
  Prix_PRO NUMERIC(8,2),
  StockRestant_PRO INTEGER,
  Modification_PRO TIMESTAMP,
  ID_CAT INTEGER
  )
AS $$
  BEGIN
  RETURN QUERY
  SELECT ph.ID_PRO, ph.Intitule_PRO, ph.Description_PRO, ph.Prix_PRO, ph.StockRestant_PRO, ph.Modification_PRO, ph.ID_CAT
    FROM PRODUIT_HIST ph
  WHERE ph.ID_PRO = $1 AND ph.Modification_PRO BETWEEN $2 AND $3+1
  ORDER BY ph.Modification_PRO DESC;
  END;  
  $$
LANGUAGE plpgsql
;



--- Fonction GetProduitHistorique avec paramètres dates (date basse, date haute)
CREATE OR REPLACE FUNCTION GetProduitHistorique(dateMin DATE, dateMax DATE) RETURNS TABLE (
  ID_PROHIST INTEGER,
  ID_PRO INTEGER,
  Intitule_PRO VARCHAR(50),
  Description_PRO VARCHAR(200),
  Prix_PRO NUMERIC(8,2),
  StockRestant_PRO INTEGER,
  Modification_PRO TIMESTAMP,
  ID_CAT INTEGER
  )
AS $$
  BEGIN
  RETURN QUERY
  SELECT ph.ID_PROHIST, ph.ID_PRO, ph.Intitule_PRO, ph.Description_PRO, ph.Prix_PRO, ph.StockRestant_PRO, ph.Modification_PRO, ph.ID_CAT
    FROM PRODUIT_HIST ph
  WHERE ph.Modification_PRO BETWEEN $1 AND $2+1
  ORDER BY ph.ID_PROHIST DESC;
  END;  
  $$
LANGUAGE plpgsql
;
